﻿using System;
using System.Data;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using System.Globalization;
using ContractManagementSystem.Entity;

namespace ContractManagementSystem.DataAccess
{
    public class ConsultantDataAccess : LogDataAccess
    {
        private const string UserName = "Admin";

        /// <summary>
        /// Function Select all Consultant
        /// Author: Group2.DucDM
        /// </summary>
        /// <param name="activeType"></param>
        /// <returns></returns>
        public DataTable FetchAll(int activeType)
        {
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            dataTable.Columns.Add("ConsultantId", typeof(Guid));
            dataTable.Columns.Add("UserId", typeof(Guid));
            dataTable.Columns.Add("BirthDate", typeof(DateTime));
            dataTable.Columns.Add("FullName", typeof(string));
            dataTable.Columns.Add("Gender", typeof(string));
            dataTable.Columns.Add("Email", typeof(string));
            dataTable.Columns.Add("PhoneNumber", typeof(string));
            dataTable.Columns.Add("IsActive", typeof(bool));
            dataTable.Columns.Add("STT", typeof(int));
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }
            try
            {
                //log event          
                WriteEvent("Selecting...", UserName);
                int i = 0;
                var sqlCommand = new SqlCommand("sp_Consultant_SelectAll", CreateConnection()) { CommandType = CommandType.StoredProcedure };
                sqlCommand.Parameters.Add(new SqlParameter("@ActiveType", activeType));
                var sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader.HasRows)
                {
                    while (sqlDataReader.Read())
                    {
                        var consultantId = new Guid(sqlDataReader["ConsultantId"].ToString());
                        var userId = new Guid(sqlDataReader["UserId"].ToString());
                        var birthDate = DateTime.Parse(sqlDataReader["BirthDate"].ToString(), CultureInfo.CurrentCulture);
                        var fullName = sqlDataReader["FullName"].ToString();
                        var gender = FetchGender(int.Parse(sqlDataReader["Gender"].ToString(), CultureInfo.CurrentCulture));
                        var email = sqlDataReader["Email"].ToString();
                        var phoneNumber = sqlDataReader["PhoneNumber"].ToString();
                        var isActive = bool.Parse(sqlDataReader["IsActive"].ToString());
                        if (i == 15)
                        {
                            i = 0;
                        }
                        dataTable.Rows.Add(consultantId, userId, birthDate, fullName, gender, email, phoneNumber, isActive, i);
                        i++;
                    }
                    //log event
                    //WriteEvent("Select success!", UserName);
                }
            }
            catch (SqlException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
            }
            finally
            {
                CloseConnection();
            }

            return dataTable;
        }

        /// <summary>
        /// Function SearchFullName
        /// Author: Group2.DucDM
        /// </summary>
        /// <param name="name"></param>
        /// <returns></returns>
        public DataTable SearchFullName(string name)
        {
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            dataTable.Columns.Add("ConsultantId", typeof(Guid));
            dataTable.Columns.Add("UserId", typeof(Guid));
            dataTable.Columns.Add("BirthDate", typeof(DateTime));
            dataTable.Columns.Add("FullName", typeof(string));
            dataTable.Columns.Add("Gender", typeof(string));
            dataTable.Columns.Add("Email", typeof(string));
            dataTable.Columns.Add("PhoneNumber", typeof(string));
            dataTable.Columns.Add("IsActive", typeof(bool));
            dataTable.Columns.Add("STT", typeof(int));
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }
            try
            {
                //log event          
                WriteEvent("Selecting...", UserName);
                int i = 0;
                var sqlCommand = new SqlCommand("sp_Consultant_SearchFullName", CreateConnection()) { CommandType = CommandType.StoredProcedure };
                sqlCommand.Parameters.Add(new SqlParameter("@FullName", name));
                var sqlDataReader = sqlCommand.ExecuteReader();
                if (sqlDataReader.HasRows)
                {
                    while (sqlDataReader.Read())
                    {
                        var consultantId = new Guid(sqlDataReader["ConsultantId"].ToString());
                        var userId = new Guid(sqlDataReader["UserId"].ToString());
                        var birthDate = DateTime.Parse(sqlDataReader["BirthDate"].ToString(), CultureInfo.CurrentCulture);
                        var fullName = sqlDataReader["FullName"].ToString();
                        var gender = FetchGender(int.Parse(sqlDataReader["Gender"].ToString(), CultureInfo.CurrentCulture));
                        var email = sqlDataReader["Email"].ToString();
                        var phoneNumber = sqlDataReader["PhoneNumber"].ToString();
                        var isActive = bool.Parse(sqlDataReader["IsActive"].ToString());
                        if (i == 14)
                        {
                            i = 0;
                        }
                        dataTable.Rows.Add(consultantId, userId, birthDate, fullName, gender, email, phoneNumber, isActive, i);
                        i++;
                    }
                    
                }
                //log event
                //WriteEvent("Select success!", UserName);
            }
            catch (SqlException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
            }
            finally
            {
                CloseConnection();
            }
            
            return dataTable;
        }

        /// <summary>
        /// Function FetchGender
        /// Author: Group2.DucDM
        /// </summary>
        /// <param name="gender"></param>
        /// <returns></returns>
        public static string FetchGender(int gender)
        {
            string gengertype;
            if (gender == 0)
            {
                gengertype = "Male";
            }
            else if (gender == 1)
            {
                gengertype = "Female";
            }
            else
            {
                gengertype = "Other";
            }
            return gengertype;
        }

        /// <summary>
        /// Function Select By Id Consultant
        /// Author: Group2.DucDM
        /// </summary>
        /// <param name="consultantEntity"></param>
        /// <returns></returns>
        public DataTable FetchById(ConsultantEntity consultantEntity)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return null;
            }
            var dataTable = new DataTable { Locale = CultureInfo.CurrentCulture };
            try
            {
                //log event                
                WriteEvent("Selecting...", UserName);
                var sqlCommand = new SqlCommand("sp_Consultant_GetByID", CreateConnection()) { CommandType = CommandType.StoredProcedure };
                sqlCommand.Parameters.Add(new SqlParameter("@ConsultantId", consultantEntity.ConsultantId));
                var dataAdapter = new SqlDataAdapter(sqlCommand);
                dataAdapter.Fill(dataTable);
                //log event
                WriteEvent("Select success!", UserName);
            }
            catch (SqlException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
            }
            catch (NullReferenceException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
            }
            catch (SqlTypeException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
            }
            catch (FormatException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
            }
            finally
            {
                CloseConnection();
            }
            
            return dataTable;
        }

        /// <summary>
        /// Function Inert Consultant
        /// Author: Group2.DucDM
        /// </summary>
        /// <param name="consultantEntity"></param>
        /// <returns></returns>
        public bool Insert(ConsultantEntity consultantEntity)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            try
            {
                //log event                
                WriteEvent("Inserting...", UserName);
                var sqlCommand = new SqlCommand("sp_Consultant_Insert", CreateConnection()) { CommandType = CommandType.StoredProcedure };
                sqlCommand.Parameters.Add(new SqlParameter("@UserId", consultantEntity.UserId));
                sqlCommand.Parameters.Add(new SqlParameter("@BirthDate", consultantEntity.Birthday));
                sqlCommand.Parameters.Add(new SqlParameter("@FullName", consultantEntity.FullName));
                sqlCommand.Parameters.Add(new SqlParameter("@Gender", consultantEntity.Gender));
                sqlCommand.Parameters.Add(new SqlParameter("@Email", consultantEntity.Email));
                sqlCommand.Parameters.Add(new SqlParameter("@PhoneNumber", consultantEntity.PhoneNumber));
                sqlCommand.ExecuteNonQuery();
                //log event
                WriteEvent("Insert success!", UserName);
            }
            catch (SqlException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Insert fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            catch (NullReferenceException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            catch (SqlTypeException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            catch (FormatException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            finally
            {
                CloseConnection();
            }

            return true;
        }

        /// <summary>
        /// Function Update Consultant
        /// Author: Group2.DucDM
        /// </summary>
        /// <param name="consultantEntity"></param>
        /// <returns></returns>
        public bool Update(ConsultantEntity consultantEntity)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            try
            {
                //log event                
                WriteEvent("Updating...", UserName);
                var sqlCommand = new SqlCommand("sp_Consultant_Update", CreateConnection()) { CommandType = CommandType.StoredProcedure };
                sqlCommand.Parameters.Add(new SqlParameter("@ConsultantId", consultantEntity.ConsultantId));
                sqlCommand.Parameters.Add(new SqlParameter("@BirthDate", consultantEntity.Birthday));
                sqlCommand.Parameters.Add(new SqlParameter("@FullName", consultantEntity.FullName));
                sqlCommand.Parameters.Add(new SqlParameter("@Gender", consultantEntity.Gender));
                sqlCommand.Parameters.Add(new SqlParameter("@Email", consultantEntity.Email));
                sqlCommand.Parameters.Add(new SqlParameter("@PhoneNumber", consultantEntity.PhoneNumber));
                sqlCommand.Parameters.Add(new SqlParameter("@IsActive", consultantEntity.IsActive));
                sqlCommand.ExecuteNonQuery();
                //log event
                WriteEvent("Update success!", UserName);
            }
            catch (SqlException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Update fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
            }
            catch (NullReferenceException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            catch (SqlTypeException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            catch (FormatException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            finally
            {
                CloseConnection();
            }

            return true;
        }

        /// <summary>
        /// Function Delete Consultant
        /// Author: Group2.DucDM
        /// </summary>
        /// <param name="consultantEntity"></param>
        /// <returns></returns>
        public bool Delete(ConsultantEntity consultantEntity)
        {
            if (!string.IsNullOrEmpty(ErrorMessage))
            {
                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            try
            {
                //log event                
                WriteEvent("Deleting...", UserName);
                var sqlCommand = new SqlCommand("sp_Consultant_Delete", CreateConnection()) { CommandType = CommandType.StoredProcedure };
                sqlCommand.Parameters.Add(new SqlParameter("@ConsultantId", consultantEntity.ConsultantId));
                sqlCommand.ExecuteNonQuery();
                //log event
                WriteEvent("Delete success!", UserName);
            }
            catch (SqlException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Delete fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
            }
            catch (NullReferenceException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            catch (SqlTypeException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            catch (FormatException e)
            {
                ErrorMessage = e.Message;
                //log event
                WriteEvent("Select fail!", UserName);

                //log error
                WriteError(ErrorMessage, UserName);
                return false;
            }
            finally
            {
                CloseConnection();
            }

            return true;
        }
    }
}
